package com.easa.dao;

import com.easa.bean.CurrentWeek;
import com.easa.entity.Semester;
import com.easa.entity.Weektable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Repository
@Transactional
public class WeektableDaoImpl implements WeektableDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Weektable findWeekidByNow(Date today){
        String sql = "SELECT * FROM weektable WHERE ? between weekStartDate AND weekEndDate";
        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat dateFormat = new SimpleDateFormat(formatDate);
        String todayStr = dateFormat.format(today);
        Weektable weektable;
        try {
            weektable = jdbcTemplate.queryForObject(sql, new Object[]{todayStr}, new BeanPropertyRowMapper<Weektable>(Weektable.class));
        }catch (Exception e){
            System.out.println(e);
            weektable = null;
        }
        return weektable;
    }

    /*查询学期名字*/
    public String findSemesterName(String semester){
        String sql = "SELECT * FROM semester WHERE semesterId = ?";

        Semester semesterMsg;
        try {
            semesterMsg = jdbcTemplate.queryForObject(sql, new Object[]{semester}, new BeanPropertyRowMapper<Semester>(Semester.class));
        }catch (Exception e){
            System.out.println(e);
            semesterMsg = null;
            return null;
        }
        return semesterMsg.getSemesterName();
    }

    /*查询所有的学期*/
    public List<Semester> findSemester(){
        String sql = "SELECT * FROM semester ORDER BY semesterStartDate DESC ";
        List<Semester> semesterMsg;
        try {
            semesterMsg = jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper(Semester.class));
        }catch (Exception e){
            System.out.println(e);
            semesterMsg = null;
        }
        return semesterMsg;
    }

    /*查找当前周*/
    public CurrentWeek findCurrentWeek(){
        Date currentDate = new Date();
        String sql = "SELECT wt.weekid,st.semesterName,wt.weekNum FROM weektable wt,semester st WHERE (? BETWEEN wt.weekStartDate AND wt.weekEndDate) AND wt.semester = st.semesterId";

        CurrentWeek currentWeek;
        try {
            currentWeek = jdbcTemplate.queryForObject(sql, new Object[]{currentDate}, new BeanPropertyRowMapper<CurrentWeek>(CurrentWeek.class));
        }catch (Exception e){
            System.out.println(e);
            currentWeek = null;
        }
        if (currentWeek == null){
            currentWeek.setWeekid("notInClassWeek");
            currentWeek.setSemesterName("notInSemester");
            currentWeek.setWeekNum(0);
        }
        return currentWeek;
    }

    /*中文学期找学期编号*/
    public String findSemesterId(String semesterName){
        String sql = "SELECT * FROM semester WHERE semesterName = ?";

        Semester semesterMsg;
        try {
            semesterMsg = jdbcTemplate.queryForObject(sql, new Object[]{semesterName}, new BeanPropertyRowMapper<Semester>(Semester.class));
        }catch (Exception e){
            System.out.println(e);
            semesterMsg = null;
            return null;
        }
        return semesterMsg.getSemesterId();
    }

    public Weektable findWeekidBydateStr(String searchDate){

        String sql = "SELECT * FROM weektable WHERE ? between weekStartDate AND weekEndDate";

        Weektable weektable;
        try {
            weektable = jdbcTemplate.queryForObject(sql, new Object[]{searchDate}, new BeanPropertyRowMapper<Weektable>(Weektable.class));
        }catch (Exception e){
            System.out.println(e);
            weektable = null;
        }
        return weektable;
    }

    public Weektable findWeekById(String weekid){
        String sql = "SELECT * FROM weektable WHERE weekid = ?";

        Weektable weektable;
        try {
            weektable = jdbcTemplate.queryForObject(sql, new Object[]{weekid}, new BeanPropertyRowMapper<Weektable>(Weektable.class));
        }catch (Exception e){
            System.out.println(e);
            weektable = null;
        }
        return weektable;
    }

    //根据id获取学期（后台）
    public Semester getSemesterById(String semesterId){
        String sql = "SELECT * FROM semester WHERE semesterId = ?";

        Semester semesterMsg;
        try {
            semesterMsg = jdbcTemplate.queryForObject(sql, new Object[]{semesterId}, new BeanPropertyRowMapper<Semester>(Semester.class));
        }catch (Exception e){
            System.out.println(e);
            semesterMsg = null;
            return null;
        }
        return semesterMsg;
    }

    //根据学校SemesterId查找semester
    public List<Weektable> findWeekBySemesterId(String semesterId){
        String sql = "SELECT * FROM weektable WHERE semester = ? ORDER BY weekStartDate";

        List<Weektable> weektables;
        try {
            weektables = jdbcTemplate.query(sql, new Object[]{semesterId}, new BeanPropertyRowMapper(Weektable.class));
        }catch (Exception e){
            System.out.println(e);
            weektables = null;
            return null;
        }
        return weektables;
    }

    //保存学期
    public int setSemeter(Semester semester){
        String sql = "INSERT INTO semester(semesterId,semesterName,semesterStartDate,semesterEndDate,weekQuantity) VALUES(?,?,?,?,?)";
        try {
            jdbcTemplate.update(sql, new Object[]{semester.getSemesterId(), semester.getSemesterName(),
                    semester.getSemesterStartDate(),semester.getSemesterEndDate(),semester.getWeekQuantity()});
        }catch (Exception e){
            System.out.println(e);
            return 1;
        }
        return 0;
    }

    //保存周次
    public int setWeektable(Weektable weektable){
        String sql = "INSERT INTO weektable(weekid,weekStartDate,weekEndDate,weekNum,semester) VALUES(?,?,?,?,?)";
        try {
            jdbcTemplate.update(sql, new Object[]{weektable.getWeekid(), weektable.getWeekStartDate(),
                    weektable.getWeekEndDate(),weektable.getWeekNum(),weektable.getSemester()});
        }catch (Exception e){
            System.out.println(e);
            return 1;
        }
        return 0;
    }

    //删除该学期的周次
    public void deleteWeeksBySid(String semesterId){
        String sql = "DELETE FROM weektable WHERE semester = ? ";
        try {
            jdbcTemplate.update(sql, new Object[]{semesterId});
        }catch (Exception e){
            System.out.println(e);
        }
    }

    //删除该学期
    public void deleteSemester(String semesterId){
        String sql = "DELETE FROM semester WHERE semesterId = ? ";
        try {
            jdbcTemplate.update(sql, new Object[]{semesterId});
        }catch (Exception e){
            System.out.println(e);
        }
    }
}
